package dao;

import bean.Area;
import bean.Goods;
import bean.People;
import bean.Status;
import util.JDBCutil;
import util.SQLUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by young on 2017/6/26.
 */
public class GoodsDao {

    private Connection connection = JDBCutil.getConnection();

    public List<Goods> goodsList() {
        List<Goods> goodsList = new ArrayList<>();
        String sql = "SELECT * FROM goods,people,status,area WHERE goods.people_id=people.id AND goods.area_id=area.id AND goods.status_id=status.id";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                Goods goods = new Goods();
                goods.setId(set.getInt("goods.id"));
                goods.setName(set.getString("goods.name"));
                goods.setSummary(set.getString("summary"));
                Area area = new Area();
                area.setName(set.getString("area.name"));
                goods.setArea(area);
                People people = new People();
                people.setName(set.getString("people.name"));
                goods.setPeople(people);
                Status status = new Status();
                status.setState(set.getString("state"));
                goods.setStatus(status);
                goodsList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsList;
    }

    public Goods getGoods(int id) {
        Goods goods = new Goods();
        String sql = "SELECT * FROM goods WHERE id=?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                goods.setId(set.getInt("id"));
                goods.setName(set.getString("name"));
                goods.setSummary(set.getString("summary"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("goods = " + goods.toString());
        return goods;
    }

    public int addGoods(Goods goods) {
        int row = 0;
        String sql = "INSERT INTO goods (name, summary, people_id, status_id, area_id) VALUES (?,?,?,?,?);";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, goods.getName());
            ps.setString(2, goods.getSummary());
            ps.setInt(3, goods.getPeople().getId());
            ps.setInt(4, goods.getStatus().getId());
            ps.setInt(5, goods.getArea().getId());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }


    public int deleteGoods(int id) {
        int row;
        String sql = "DELETE FROM goods WHERE id=?";
        row = SQLUtil.deleteInfo(sql, id);
        return row;
    }

    public int updateGoods(Goods goods) {
        int row = 0;
        String sql = "UPDATE goods SET name = ?,summary=?,people_id=?,area_id=?,status_id=? WHERE id=?;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, goods.getName());
            ps.setString(2, goods.getSummary());
            ps.setInt(3, goods.getPeople().getId());
            ps.setInt(4, goods.getArea().getId());
            ps.setInt(5, goods.getStatus().getId());
            ps.setInt(6, goods.getId());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
}
